﻿/*
 * 文件名：Class.cs
 * 类名：XHCTMS.DAL.Class
 * 创建人：季崇华  
 * 创建时间：2013-5-21
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 班级实体类操作
    /// </summary>
    public class Class
    {
        /// <summary>
        /// 增加班级
        /// </summary>
        /// <param name="Class">班级</param>
        /// <returns>返回类型：int 1/成功 0/失败 -1/异常</returns>
        public int Add(MODEL.Class Class)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"insert into class values(@Class_Name,@Class_Sub_Id,
                                @Class_Teacher_Id, @Class_dep_id, 1)");
            SqlParameter[] para = { 

                                      new SqlParameter("@Class_Name", SqlDbType.VarChar, 32), 
                                      new SqlParameter("@Class_Sub_Id", SqlDbType.Int),
                                      new SqlParameter("@Class_Teacher_id", SqlDbType.Int),
                                      new SqlParameter("@Class_dep_id", SqlDbType.Int)
                                  };

            para[0].Value = Class.ClassName;
            para[1].Value = Class.ClassSubId;
            para[2].Value = Class.ClassTeacherId;
            para[3].Value = Class.ClassDepId;
            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 删除班级（班级毕业同时要让班级与学生都设置为毕业状态）
        /// </summary>
        /// <param name="classid">班级号</param>
        /// <returns>返回类型:int 1/成功 0/失败 -1/异常</returns>
        public int Delete(int classid)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"update class set class_flag=0 where class_id=@classid; 
                                update student set student_state=0 where student_class_id=@classid");
            SqlParameter[] paras = {
                                       new SqlParameter("@classid", SqlDbType.Int)
                                   };
            paras[0].Value = classid;

            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 按班级号修改班级信息
        /// </summary>
        /// <param name="Class">要修改的信息和班级号</param>
        /// <returns>返回类型:int 1/成功 0/失败 -1/异常</returns>
        public int Update(int ClassId, MODEL.Class Class)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"update class set Class_Name=@ClassName,
                                Class_Sub_Id=@Class_Sub_Id,Class_Teacher_Id=@Class_Teacher_Id,  
                                Class_Dep_Id =@Class_Dep_Id ,                     
                                Class_Flag=@ClassFlag where Class_ID=@Class_ID");
            SqlParameter[] para = { 
                                      new SqlParameter("@Class_ID", SqlDbType.Int),
                                      new SqlParameter("@ClassName", SqlDbType.VarChar, 32), 
                                      new SqlParameter("@Class_Sub_Id", SqlDbType.Int),
                                      new SqlParameter("@Class_Teacher_id", SqlDbType.Int),
                                      new SqlParameter("@Class_Dep_Id",SqlDbType.Int),
                                      new SqlParameter("@ClassFlag", SqlDbType.Bit)
                                  };
            para[0].Value = ClassId;
            para[1].Value = Class.ClassName;
            para[2].Value = Class.ClassSubId;
            para[3].Value = Class.ClassTeacherId;
            para[4].Value = Class.ClassDepId;
            para[5].Value = Class.ClassFlag;

            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 分页获取班级信息
        /// </summary>
        /// <param name="PageIndex">页面大小</param>
        /// <param name="PageSize">当前页号</param>
        /// <returns>返回类型：int 1/成功, 0/失败, -1/异常</returns>
        public DataTable Formation(int PageIndex=1,int PageSize=50 )
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select top(50) class_id,class_name,sub_name,[user_name],dep_name from 
                                class  left join sub on class_sub_id=sub_id  left join [user] on class_teacher_id=[user_id] 
                                left join department  on class_dep_id=dep_id where class_flag =1 and class_id not in
                                (select top (@PageSize*(@PageIndex-1))class_id from class left join sub on class_sub_id=sub_id  
                                left join [user] on class_teacher_id=[user_id] left join department on class_dep_id=dep_Id  order by sub_name )
                                order by sub_name");
            SqlParameter[] para = { 
                                       new SqlParameter("@PageIndex", SqlDbType.Int),
                                       new SqlParameter("@PageSize",SqlDbType.Int)

                                   };

            para[0].Value = PageIndex;
            para[1].Value = PageSize;

            return new DBHELP.Common().GetDataTable(queryString.ToString(), para);
        }

        public int MaxPage(string[] parasValue)
        {
            DataTable dt = new DataTable();
            string[] parasStr = new string[] { " tc.class_name =", " ts.sub_name =", " tu.[user_name] ="," td.dep_name =" ,"tc.class_flag =" };
            string[] parasName = new string[]{"@class_name","@sub_name","@user_name","@dep_name","@class_flag"};
            if (parasValue.Length > parasStr.Length)
            {
                return -101; //数组错误
            }
            List<SqlParameter> paras = new List<SqlParameter> { };

            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select COUNT(class_id) from 
                                class tc left join sub ts on tc.class_sub_id=ts.sub_id  left join [user] tu on tc.class_teacher_id=tu.[user_id] 
                                left join department td on tc.class_dep_id= td.dep_id ");
            bool isFirst= true;
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    switch (isFirst)
                    {
                        case true:
                            queryString.Append(" where " + parasStr[count] + parasName[count]);
                            paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                            isFirst = false;
                            break;
                        default:
                            queryString.Append(" and " + parasStr[count] + parasName[count]);
                            paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                            break;
                    }               
                }

            }

            return int.Parse(new DBHELP.Common().GetDataTable(queryString.ToString(), paras).Rows[0][0].ToString())/50+1;

        }





        public DataTable Search(string[] parasValue,int PageIndex = 1, int PageSize = 50)
        {

            DataTable dt = new DataTable();
            string[] parasStr = new string[] { " class_name =", " sub_name =", " [user_name] ="," dep_name =" ,"class_flag =" };
            string[] parasName = new string[]{"@class_name","@sub_name","@user_name","@dep_name","@class_flag"};
            if (parasValue.Length > parasStr.Length)
            {
                dt.TableName = "ArraryLengthError";
                return dt;
            }

            List<SqlParameter> paras = new List<SqlParameter> { };

            StringBuilder queryString = new StringBuilder();
            StringBuilder queryString2 = new StringBuilder();
            queryString.Append(@"select top(50) class_id,class_name,sub_name,[user_name],dep_name from 
                                class  left join sub on class_sub_id=sub_id  left join [user] on class_teacher_id=[user_id] 
                                left join department  on class_dep_id=dep_id  ");
            queryString2.Append(@" and class_id not in
                                (select top (@PageSize*(@PageIndex-1))class_id from class left join sub on class_sub_id=sub_id  
                                left join [user] on class_teacher_id=[user_id] left join department on class_dep_id=dep_Id  
                                ");
            bool isFirst = true;
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    switch (isFirst)
                    {
                        case true:
                            queryString2.Append(" where " + parasStr[count] + parasName[count]);
                            queryString.Append(" where " + parasStr[count] + parasName[count]);
                            paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                            isFirst = false;
                            break;
                        case false:

                            queryString.Append(" and " + parasStr[count] + parasName[count]);
                            queryString2.Append(" and " + parasStr[count] + parasName[count]);
                            paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                            break;
                    }

                }

            }
            paras.Add(new SqlParameter("@PageSize", PageSize));
            paras.Add(new SqlParameter("@PageIndex", PageIndex));

            queryString.Append(queryString2.ToString() + " order by sub_name ");
            queryString.Append(" ) order by sub_name ");

            return new DBHELP.Common().GetDataTable(queryString.ToString(), paras);
        }


        /// <summary>
        /// 通过专业名。得到所有专业下的班级
        /// </summary>
        /// <param name="subName"></param>
        /// <returns></returns>
        public DataTable GetClassName()
        {
           StringBuilder queryString = new StringBuilder();
            queryString.Append("select class_id,class_name from class order by class_name");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        /// <summary>
        /// 获得班级全称
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllClassName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select tc.class_id,ts.sub_name + convert(varchar(2),ts.sub_years)+'年制  '+ class_name as class_allname
 from class tc left join sub ts on tc.class_sub_id = ts.sub_id where tc.class_flag =1 order by ts.sub_name,ts.sub_years");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }



        /// <summary>
        /// 根据班级号得到班级状态
        /// </summary>
        /// <param name="classID"></param>
        /// <returns></returns>
        public string GetClassFlag(int classID)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select class_flag from class where class_id = @classID");
            SqlParameter[] paras = { new SqlParameter("@classID", classID) };
            string flag = new DBHELP.Common().GetDataTable(queryString.ToString(), paras).Rows[0][0].ToString();
            return flag;
        }

        /// <summary>
        /// 班主任获取所带的班级名
        /// </summary>
        /// <param name="classTeaID"></param>
        /// <returns></returns>
        public DataTable ClassTeaGetClassName(int classTeaID)
        {
            string strSql = @" select tcl.class_id,tcl.class_name from class tcl left join [user] tu on tu.[user_id] = tcl.class_teacher_id
    where tcl.class_flag = 1 and tu.[user_id] = @classTeaID";
            SqlParameter[] paras = { new SqlParameter("@classTeaID", classTeaID) };
            return new DBHELP.Common().GetDataTable(strSql, paras);
 
        }

        
    }
}
